#nullable enable
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using ERP.Api.Models;
using AgriSmart.ErrorCode;
using ERP.Infeartructure;
using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;
using REP.Domain.Entities;
using ERP.ErrorCode;
using System.Text;
using Microsoft.AspNetCore.Http;
using System.IO;
using ClosedXML.Excel;

namespace ERP.Api.Controllers
{
    /// <summary>
    /// BOM 接口
    /// </summary>
    [ApiController]
    [Route("api/[controller]")]
    public class BomsController : ControllerBase
    {
        private readonly ErpDbContext _db;

        public BomsController(ErpDbContext db)
        {
            _db = db;
        }

        /// <summary>
        /// 新增 BOM（含配件、工艺路线、附件、操作日志）
        /// </summary>
        [HttpPost]
        public async Task<ActionResult<ApiResult<Guid?>>> Create([FromBody] BomCreateDto dto)
        {
            // 简单空值防御
            dto ??= new BomCreateDto();

            // 启动事务，避免部分成功
            await using var tran = await _db.Database.BeginTransactionAsync();

            try
            {
                // 1. 组装 BOM 主表
                var bomId = Guid.NewGuid();
                var bom = new Bom
                {
                    Id = bomId,
                    Code = dto.BomCode,
                    Name = dto.ProductName,
                    Version = dto.VersionTag,
                    Remark = dto.Remark,
                    CreatedBy = dto.Operator,
                    CreatedAt = DateTime.Now,

                    BomCode = dto.BomCode,
                    SystemCode = dto.SystemCode,
                    ProductType = dto.ProductType,
                    ProductCode = dto.ProductCode,
                    ProductName = dto.ProductName,
                    SpecModel = dto.SpecModel,
                    Unit = dto.Unit,
                    VersionTag = dto.VersionTag,
                    DrawingNo = dto.DrawingNo,
                    IsDefault = dto.IsDefault,
                    DailyOutput = dto.DailyOutput,
                    EffectiveDate = dto.EffectiveDate,
                    ExpireDate = dto.ExpireDate,
                };

                // 2. 配件明细
                if (dto.Accessories != null && dto.Accessories.Count > 0)
                {
                    bom.Accessories ??= new List<BomAccessory>();
                    foreach (var a in dto.Accessories)
                    {
                        bom.Accessories.Add(new BomAccessory
                        {
                            Id = Guid.NewGuid(),
                            BomId = bomId,
                            ItemCode = a.ItemCode,
                            ItemName = a.ItemName,
                            SpecModel = a.SpecModel,
                            Unit = a.Unit,
                            ItemType = a.ItemType,
                            Category = a.Category,
                            Brand = a.Brand,
                            TotalStock = a.TotalStock,
                            PurchasePrice = a.PurchasePrice,
                            SalesPrice = a.SalesPrice,
                            Qty = a.Qty,
                            Sort = a.SortNo,
                            Remark = a.Remark
                        });
                    }
                }

                // 3. 工艺路线关联（校验传入的 RouteId 是否存在）
                var routeLinks = new List<BomProcessRoute>();
                if (dto.ProcessRoutes != null && dto.ProcessRoutes.Count > 0)
                {
                    var wantIds = dto.ProcessRoutes.Where(x => x.RouteId.HasValue && x.RouteId != Guid.Empty).Select(x => x.RouteId!.Value).ToList();
                    var routes = await _db.ProcessRoutes!
                        .Where(r => r.Id != null && wantIds.Contains(r.Id.Value))
                        .ToListAsync();
                    var dict = routes.ToDictionary(r => r.Id!.Value);
                    foreach (var pr in dto.ProcessRoutes)
                    {
                        if (pr.RouteId == null || pr.RouteId == Guid.Empty) continue;
                        dict.TryGetValue(pr.RouteId.Value, out var baseRt);
                        routeLinks.Add(new BomProcessRoute
                        {
                            Id = Guid.NewGuid(),
                            BomId = bomId,
                            RouteId = pr.RouteId,
                            RouteCode = pr.RouteCode ?? baseRt?.RouteCode,
                            RouteName = pr.RouteName ?? baseRt?.RouteName,
                            SelectedAt = DateTime.Now,
                            Sort = pr.Sort ?? 0,
                            Owner = pr.Owner,
                            Department = pr.Department,
                            UnitWorkHours = pr.UnitWorkHours,
                            Remark = pr.Remark
                        });
                    }
                }
                else if (dto.RouteIds != null && dto.RouteIds.Count > 0)
                {
                    var wantIds = dto.RouteIds.Where(id => id != Guid.Empty).ToList();
                    var routes = await _db.ProcessRoutes!
                        .Where(r => r.Id != null && wantIds.Contains(r.Id.Value))
                        .ToListAsync();
                    foreach (var rt in routes)
                    {
                        routeLinks.Add(new BomProcessRoute
                        {
                            Id = Guid.NewGuid(),
                            BomId = bomId,
                            RouteId = rt.Id,
                            RouteCode = rt.RouteCode,
                            RouteName = rt.RouteName,
                            SelectedAt = DateTime.Now,
                            Sort = 0
                        });
                    }
                }

                // 4. 附件
                var attachments = new List<Attachment>();
                if (dto.Attachments != null && dto.Attachments.Count > 0)
                {
                    foreach (var f in dto.Attachments)
                    {
                        attachments.Add(new Attachment
                        {
                            Id = Guid.NewGuid(),
                            BomId = bomId,
                            FileName = f.FileName,
                            OriginalName = f.OriginalFileName,
                            FileSize = f.FileSize,
                            ContentType = f.ContentType,
                            Url = f.Url,
                            UploadedBy = dto.Operator,
                            UploadedAt = DateTime.Now,
                            Description = f.Remark
                        });
                    }
                }

                // 5. 操作日志
                var log = new OperationLog
                {
                    Id = Guid.NewGuid(),
                    BomId = bomId,
                    ActionType = "Create",
                    Operator = dto.Operator,
                    OperatedAt = DateTime.Now,
                    Details = "新增BOM",
                    SourceIp = HttpContext.Connection.RemoteIpAddress?.ToString()
                };

                // 6. 写库
                await _db.Boms!.AddAsync(bom);
                if (routeLinks.Count > 0) await _db.BomProcessRoutes!.AddRangeAsync(routeLinks);
                if (attachments.Count > 0) await _db.Attachments!.AddRangeAsync(attachments);
                await _db.OperationLogs!.AddAsync(log);
                await _db.SaveChangesAsync();
                await tran.CommitAsync();

                var successMsg = "操作成功";
                if (dto.RouteIds != null && dto.RouteIds.Count > 0)
                {
                    var wantIds = dto.RouteIds.Where(id => id != Guid.Empty).ToList();
                    var foundIds = routeLinks.Select(r => r.RouteId!.Value).ToHashSet();
                    var missing = wantIds.Where(id => !foundIds.Contains(id)).ToList();
                    if (missing.Count > 0)
                    {
                        successMsg += $"（已忽略不存在的工艺路线：{string.Join(",", missing)}）";
                    }
                }

                return new ApiResult<Guid?>
                {
                    Code = BaseEnums.Success,
                    Msg = successMsg,
                    Data = bomId
                };
            }
            catch (Exception ex)
            {
                await tran.RollbackAsync();
                return new ApiResult<Guid?>
                {
                    Code = BaseEnums.InternalServerError,
                    Msg = ex.GetBaseException().Message,
                    Data = null
                };
            }
        }

        /// <summary>
        /// BOM 列表查询（分页 + 筛选）
        /// </summary>
        [HttpGet]
        public async Task<ActionResult<ApiResult<ApiPaging<BomListItemDto>>>> Query([FromQuery] BomQueryDto q)
        {
            q ??= new BomQueryDto();

            var query = _db.Boms!.AsNoTracking().AsQueryable();

            // 快速关键字：匹配 编号/主题/成品名/成品编码
            if (!string.IsNullOrWhiteSpace(q.Kw))
            {
                var kw = q.Kw.Trim();
                query = query.Where(b =>
                    (b.BomCode ?? "").Contains(kw) ||
                    (b.SystemCode ?? "").Contains(kw) ||
                    (b.Name ?? "").Contains(kw) ||
                    (b.ProductName ?? "").Contains(kw) ||
                    (b.ProductCode ?? "").Contains(kw)
                );
            }

            // 高级筛选
            if (!string.IsNullOrWhiteSpace(q.Code))
            {
                var code = q.Code.Trim();
                query = query.Where(b => (b.BomCode ?? "").Contains(code) || (b.SystemCode ?? "").Contains(code));
            }
            if (!string.IsNullOrWhiteSpace(q.Subject))
            {
                var sub = q.Subject.Trim();
                query = query.Where(b => (b.Name ?? b.ProductName ?? "").Contains(sub));
            }
            if (q.IsDefault.HasValue)
            {
                query = query.Where(b => b.IsDefault == q.IsDefault);
            }
            if (!string.IsNullOrWhiteSpace(q.Version))
            {
                var ver = q.Version.Trim();
                query = query.Where(b => (b.VersionTag ?? "").Contains(ver));
            }
            if (!string.IsNullOrWhiteSpace(q.ProductType))
            {
                var pt = q.ProductType.Trim();
                query = query.Where(b => b.ProductType == pt);
            }
            if (!string.IsNullOrWhiteSpace(q.ProductName))
            {
                var pn = q.ProductName.Trim();
                query = query.Where(b => (b.ProductName ?? "").Contains(pn));
            }
            // 日产量范围
            if (q.DailyMin.HasValue)
            {
                var min = q.DailyMin.Value;
                query = query.Where(b => (b.DailyOutput ?? 0) >= min);
            }
            if (q.DailyMax.HasValue)
            {
                var max = q.DailyMax.Value;
                query = query.Where(b => (b.DailyOutput ?? 0) <= max);
            }

            // 统计总数
            var total = await query.CountAsync();

            // 排序：按创建时间倒序（若无则按编号）
            query = query.OrderByDescending(b => b.CreatedAt).ThenByDescending(b => b.BomCode);

            // 分页
            var page = q.Page <= 0 ? 1 : q.Page;
            var pageSize = q.PageSize <= 0 ? 10 : q.PageSize;
            var rows = await query
                .Skip((page - 1) * pageSize)
                .Take(pageSize)
                .Select(b => new BomListItemDto
                {
                    Id = b.Id,
                    Code = string.IsNullOrWhiteSpace(b.BomCode) ? b.SystemCode : b.BomCode,
                    Subject = b.Name ?? b.ProductName,
                    ProductCode = b.ProductCode,
                    ProductName = b.ProductName,
                    Model = b.SpecModel,
                    Unit = b.Unit,
                    Category = b.ProductType,
                    IsDefault = b.IsDefault,
                    DailyOutput = b.DailyOutput,
                    Version = b.VersionTag,
                })
                .ToListAsync();

            var paging = new ApiPaging<BomListItemDto>
            {
                Data = rows,
                TotalCount = total,
                PageIndex = page,
                PageSize = pageSize,
                TotalPages = (int)Math.Ceiling(total / (double)pageSize)
            };

            return new ApiResult<ApiPaging<BomListItemDto>>
            {
                Code = BaseEnums.Success,
                Msg = "ok",
                Data = paging
            };
        }

        /// <summary>
        /// 获取 BOM 详情（含配件清单）
        /// </summary>
        [HttpGet("{id}")]
        public async Task<ActionResult<ApiResult<BomDetailsDto>>> Details(Guid id)
        {
            var bom = await _db.Boms!
                .AsNoTracking()
                .Include(b => b.Accessories!)
                .FirstOrDefaultAsync(b => b.Id == id);

            if (bom == null)
            {
                return new ApiResult<BomDetailsDto>
                {
                    Code = BaseEnums.NotFound,
                    Msg = "BOM不存在",
                    Data = null!
                };
            }

            // 解析损耗率：从 Remark 如 "损耗率:10%" 中提取数字
            decimal? ParseLoss(string? remark)
            {
                if (string.IsNullOrWhiteSpace(remark)) return null;
                try
                {
                    var s = remark.Replace(" ", "");
                    var i = s.IndexOf("损耗率:");
                    if (i >= 0)
                    {
                        var t = s.Substring(i + 4).Trim('%');
                        if (decimal.TryParse(t, out var v)) return v;
                    }
                }
                catch { }
                return null;
            }

            var dto = new BomDetailsDto
            {
                Id = bom.Id ?? Guid.Empty,
                Code = string.IsNullOrWhiteSpace(bom.BomCode) ? bom.SystemCode : bom.BomCode,
                Subject = bom.Name ?? bom.ProductName,
                IsDefault = bom.IsDefault,
                Version = bom.VersionTag,
                ProductType = bom.ProductType,
                ProductCode = bom.ProductCode,
                ProductName = bom.ProductName,
                SpecModel = bom.SpecModel,
                Unit = bom.Unit,
                DailyOutput = bom.DailyOutput,
                CurrentStock = (bom.Accessories ?? new List<BomAccessory>()).Sum(a => a.TotalStock ?? 0m),
                EstimatedCost = (bom.Accessories ?? new List<BomAccessory>()).Sum(a =>
                    (a.Qty ?? 0m) * (a.PurchasePrice ?? a.SalesPrice ?? 0m)
                ),
                Remark = bom.Remark,
                CreatedBy = bom.CreatedBy,
                CreatedAt = bom.CreatedAt,
                UpdatedBy = bom.UpdatedBy,
                UpdatedAt = bom.UpdatedAt,
                Accessories = (bom.Accessories ?? new List<BomAccessory>())
                    .OrderBy(a => a.Sort)
                    .Select((a, idx) => new BomAccessoryDetailDto
                    {
                        Index = idx + 1,
                        ItemCode = a.ItemCode,
                        ItemName = a.ItemName,
                        SpecModel = a.SpecModel,
                        Unit = a.Unit,
                        Brand = a.Brand,
                        Qty = a.Qty,
                        LossRate = ParseLoss(a.Remark),
                        CurrentStock = a.TotalStock ?? 0m,
                        AvailableProduce = (a.Qty.HasValue && a.Qty.Value > 0)
                            ? Math.Floor((a.TotalStock ?? 0m) / a.Qty.Value)
                            : 0m
                    })
                    .ToList()
            };

            // 补充：关联的工艺路线（简要）
            dto.ProcessRoutes = await _db.BomProcessRoutes!
                .AsNoTracking()
                .Where(x => x.BomId == bom.Id)
                .OrderBy(x => x.Sort)
                .Select(x => new BomRouteLinkDto
                {
                    RouteId = x.RouteId,
                    RouteCode = x.RouteCode,
                    RouteName = x.RouteName,
                    Sort = x.Sort,
                    SelectedAt = x.SelectedAt,
                    Owner = x.Owner,
                    Department = x.Department,
                    UnitWorkHours = x.UnitWorkHours,
                    Remark = x.Remark
                })
                .ToListAsync();

            // 补充：操作日志
            dto.Logs = await _db.OperationLogs!
                .AsNoTracking()
                .Where(x => x.BomId == bom.Id)
                .OrderByDescending(x => x.OperatedAt)
                .Select(x => new OperationLogDto
                {
                    Operator = x.Operator,
                    OperateTime = x.OperatedAt,
                    Content = x.Details ?? x.ActionType
                })
                .ToListAsync();

            return new ApiResult<BomDetailsDto>
            {
                Code = BaseEnums.Success,
                Msg = "ok",
                Data = dto
            };
        }

        /// <summary>
        /// 删除 BOM（级联删除关联明细）
        /// </summary>
        [HttpDelete("{id}")]
        public async Task<ActionResult<ApiResult<bool>>> Delete(Guid id)
        {
            var bom = await _db.Boms!.FirstOrDefaultAsync(b => b.Id == id);
            if (bom == null)
            {
                return new ApiResult<bool>
                {
                    Code = BaseEnums.NotFound,
                    Msg = "BOM不存在",
                    Data = false
                };
            }

            _db.Boms!.Remove(bom);
            await _db.SaveChangesAsync();

            return new ApiResult<bool>
            {
                Code = BaseEnums.Success,
                Msg = "删除成功",
                Data = true
            };
        }

        /// <summary>
        /// BOM 结构展开（拍平所有层级）
        /// </summary>
        [HttpGet("{id}/explode")]
        public async Task<ActionResult<ApiResult<List<ExplodedRowDto>>>> Explode(Guid id)
        {
            // 读取根BOM及其第一层配件
            var root = await _db.Boms!
                .AsNoTracking()
                .Include(b => b.Accessories!)
                .FirstOrDefaultAsync(b => b.Id == id);

            if (root == null)
            {
                return new ApiResult<List<ExplodedRowDto>>
                {
                    Code = BaseEnums.NotFound,
                    Msg = "BOM不存在",
                    Data = new List<ExplodedRowDto>()
                };
            }

            // 解析损耗率：从 Remark 如 "损耗率:10%" 中提取数字
            decimal? ParseLoss(string? remark)
            {
                if (string.IsNullOrWhiteSpace(remark)) return null;
                try
                {
                    var s = remark.Replace(" ", "");
                    var i = s.IndexOf("损耗率:");
                    if (i >= 0)
                    {
                        var t = s.Substring(i + 4).Trim('%');
                        if (decimal.TryParse(t, out var v)) return v;
                    }
                }
                catch { }
                return null;
            }

            // 选择某物料编码对应的子BOM：优先默认，其次按创建时间倒序
            async Task<Bom?> PickChildBomAsync(string? productCode)
            {
                if (string.IsNullOrWhiteSpace(productCode)) return null;
                var q = _db.Boms!
                    .AsNoTracking()
                    .Where(b => b.ProductCode == productCode);

                var list = await q
                    .OrderByDescending(b => b.IsDefault == true)
                    .ThenByDescending(b => b.CreatedAt)
                    .ToListAsync();

                var child = list.FirstOrDefault();
                if (child == null) return null;

                // 补齐其 accessories
                child.Accessories = await _db.BomAccessories!
                    .AsNoTracking()
                    .Where(a => a.BomId == child.Id)
                    .OrderBy(a => a.Sort)
                    .ToListAsync();
                return child;
            }

            var result = new List<ExplodedRowDto>();
            var visiting = new HashSet<string>(StringComparer.OrdinalIgnoreCase); // 防环：跟踪路径上的 ProductCode 或 BomCode

            // 深度优先展开
            async Task ExpandAsync(Bom parentBom, int level, decimal accQty)
            {
                var accessories = parentBom.Accessories ?? new List<BomAccessory>();
                foreach (var a in accessories.OrderBy(x => x.Sort))
                {
                    var nodeQty = a.Qty ?? 0m;
                    var loss = ParseLoss(a.Remark) ?? 0m; // 0-100
                    var nodeFactor = nodeQty * (1 + (loss / 100m));
                    var newAccQty = accQty * nodeFactor;

                    // 尝试匹配子BOM
                    Bom? childBom = await PickChildBomAsync(a.ItemCode);
                    var childKey = childBom?.ProductCode ?? string.Empty;

                    result.Add(new ExplodedRowDto
                    {
                        Level = level,
                        ParentItemCode = parentBom.ProductCode,
                        ItemCode = a.ItemCode,
                        ItemName = a.ItemName,
                        SpecModel = a.SpecModel,
                        Unit = a.Unit,
                        Brand = a.Brand,
                        NodeQty = a.Qty,
                        LossRate = loss,
                        AccQty = newAccQty,
                        BomCode = childBom?.BomCode ?? childBom?.SystemCode,
                        Version = childBom?.VersionTag
                    });

                    // 继续向下展开（若存在子BOM），并做环路防护
                    if (childBom != null && !string.IsNullOrWhiteSpace(childKey))
                    {
                        var mark = $"{childKey}|{childBom.VersionTag}";
                        if (visiting.Contains(mark))
                        {
                            // 检测到环路，跳过继续展开
                            continue;
                        }
                        visiting.Add(mark);
                        await ExpandAsync(childBom, level + 1, newAccQty);
                        visiting.Remove(mark);
                    }
                }
            }

            // 根层从1开始，累计用量起始为1（表示以1件成品为基准）
            var rootMark = $"{root.ProductCode}|{root.VersionTag}";
            visiting.Add(rootMark);
            await ExpandAsync(root, 1, 1m);
            visiting.Remove(rootMark);

            return new ApiResult<List<ExplodedRowDto>>
            {
                Code = BaseEnums.Success,
                Msg = "ok",
                Data = result
            };
        }

        /// <summary>
        /// 更新 BOM（整体覆盖：基础信息、配件、工艺路线、附件）
        /// </summary>
        [HttpPut("{id}")]
        public async Task<ActionResult<ApiResult<bool>>> Update(Guid id, [FromBody] BomCreateDto dto)
        {
            dto ??= new BomCreateDto();

            await using var tran = await _db.Database.BeginTransactionAsync();
            try
            {
                var bom = await _db.Boms!.Include(b => b.Accessories!).FirstOrDefaultAsync(b => b.Id == id);
                if (bom == null)
                {
                    return new ApiResult<bool> { Code = BaseEnums.NotFound, Msg = "BOM不存在", Data = false };
                }

                // 基础信息
                bom.Code = dto.BomCode;
                bom.BomCode = dto.BomCode;
                bom.SystemCode = dto.SystemCode;
                bom.Name = dto.ProductName ?? dto.Remark ?? bom.Name;
                bom.ProductType = dto.ProductType;
                bom.ProductCode = dto.ProductCode;
                bom.ProductName = dto.ProductName;
                bom.SpecModel = dto.SpecModel;
                bom.Unit = dto.Unit;
                bom.VersionTag = dto.VersionTag;
                bom.DrawingNo = dto.DrawingNo;
                bom.IsDefault = dto.IsDefault;
                bom.DailyOutput = dto.DailyOutput;
                bom.EffectiveDate = dto.EffectiveDate;
                bom.ExpireDate = dto.ExpireDate;
                bom.Remark = dto.Remark;
                bom.UpdatedBy = dto.Operator;
                bom.UpdatedAt = DateTime.Now;

                // 清空并重建配件
                var oldAcc = await _db.BomAccessories!.Where(a => a.BomId == id).ToListAsync();
                if (oldAcc.Count > 0) _db.BomAccessories!.RemoveRange(oldAcc);
                if (dto.Accessories != null && dto.Accessories.Count > 0)
                {
                    var newAcc = dto.Accessories.Select(a => new BomAccessory
                    {
                        Id = Guid.NewGuid(),
                        BomId = id,
                        ItemCode = a.ItemCode,
                        ItemName = a.ItemName,
                        SpecModel = a.SpecModel,
                        Unit = a.Unit,
                        ItemType = a.ItemType,
                        Category = a.Category,
                        Brand = a.Brand,
                        TotalStock = a.TotalStock,
                        PurchasePrice = a.PurchasePrice,
                        SalesPrice = a.SalesPrice,
                        Qty = a.Qty,
                        Sort = a.SortNo,
                        Remark = a.Remark
                    });
                    await _db.BomAccessories!.AddRangeAsync(newAcc);
                }

                // 清空并重建工艺路线关联
                var oldLinks = await _db.BomProcessRoutes!.Where(r => r.BomId == id).ToListAsync();
                if (oldLinks.Count > 0) _db.BomProcessRoutes!.RemoveRange(oldLinks);
                if (dto.ProcessRoutes != null && dto.ProcessRoutes.Count > 0)
                {
                    var wantIds = dto.ProcessRoutes.Where(x => x.RouteId.HasValue && x.RouteId != Guid.Empty).Select(x => x.RouteId!.Value).ToList();
                    var routes = await _db.ProcessRoutes!
                        .Where(r => r.Id != null && wantIds.Contains(r.Id.Value))
                        .ToListAsync();
                    var dict = routes.ToDictionary(r => r.Id!.Value);
                    var links = dto.ProcessRoutes.Select(pr =>
                    {
                        dict.TryGetValue(pr.RouteId!.Value, out var baseRt);
                        return new BomProcessRoute
                        {
                            Id = Guid.NewGuid(),
                            BomId = id,
                            RouteId = pr.RouteId,
                            RouteCode = pr.RouteCode ?? baseRt?.RouteCode,
                            RouteName = pr.RouteName ?? baseRt?.RouteName,
                            SelectedAt = DateTime.Now,
                            Sort = pr.Sort ?? 0,
                            Owner = pr.Owner,
                            Department = pr.Department,
                            UnitWorkHours = pr.UnitWorkHours,
                            Remark = pr.Remark
                        };
                    }).ToList();
                    await _db.BomProcessRoutes!.AddRangeAsync(links);
                }
                else if (dto.RouteIds != null && dto.RouteIds.Count > 0)
                {
                    var wantIds = dto.RouteIds.Where(g => g != Guid.Empty).ToList();
                    var routes = await _db.ProcessRoutes!
                        .Where(r => r.Id != null && wantIds.Contains(r.Id.Value))
                        .ToListAsync();
                    var links = routes.Select(rt => new BomProcessRoute
                    {
                        Id = Guid.NewGuid(),
                        BomId = id,
                        RouteId = rt.Id,
                        RouteCode = rt.RouteCode,
                        RouteName = rt.RouteName,
                        SelectedAt = DateTime.Now,
                        Sort = 0
                    }).ToList();
                    await _db.BomProcessRoutes!.AddRangeAsync(links);
                }

                // 附件整体替换
                var oldAttach = await _db.Attachments!.Where(a => a.BomId == id).ToListAsync();
                if (oldAttach.Count > 0) _db.Attachments!.RemoveRange(oldAttach);
                if (dto.Attachments != null && dto.Attachments.Count > 0)
                {
                    var newAttach = dto.Attachments.Select(f => new Attachment
                    {
                        Id = Guid.NewGuid(),
                        BomId = id,
                        FileName = f.FileName,
                        OriginalName = f.OriginalFileName,
                        FileSize = f.FileSize,
                        ContentType = f.ContentType,
                        Url = f.Url,
                        UploadedBy = dto.Operator,
                        UploadedAt = DateTime.Now,
                        Description = f.Remark
                    });
                    await _db.Attachments!.AddRangeAsync(newAttach);
                }

                // 日志
                var log = new OperationLog
                {
                    Id = Guid.NewGuid(),
                    BomId = id,
                    ActionType = "Update",
                    Operator = dto.Operator,
                    OperatedAt = DateTime.Now,
                    Details = "编辑BOM",
                    SourceIp = HttpContext.Connection.RemoteIpAddress?.ToString()
                };
                await _db.OperationLogs!.AddAsync(log);

                await _db.SaveChangesAsync();
                await tran.CommitAsync();
                return new ApiResult<bool> { Code = BaseEnums.Success, Msg = "保存成功", Data = true };
            }
            catch (Exception ex)
            {
                await tran.RollbackAsync();
                return new ApiResult<bool> { Code = BaseEnums.InternalServerError, Msg = ex.GetBaseException().Message, Data = false };
            }
        }

        /// <summary>
        /// 下载BOM导入模板（XLSX）
        /// </summary>
        [HttpGet("template")]
        public ActionResult DownloadTemplate()
        {
            using var wb = new XLWorkbook();
            var ws = wb.AddWorksheet("BOM");
            var headers = new[] { "BomCode","SystemCode","ProductType","ProductCode","ProductName","SpecModel","Unit","VersionTag","IsDefault","DailyOutput","Remark" };
            for (int i = 0; i < headers.Length; i++) ws.Cell(1, i + 1).Value = headers[i];
            var sample = new[] { "BOM20250828","","FG","P001","示例产品","型号A","件","V1","是","100","可选备注" };
            for (int i = 0; i < sample.Length; i++) ws.Cell(2, i + 1).Value = sample[i];
            ws.Range(1,1,1,headers.Length).Style.Font.Bold = true;
            ws.Columns().AdjustToContents();
            using var ms = new MemoryStream();
            wb.SaveAs(ms);
            var bytes = ms.ToArray();
            var fileName = $"BOM_Template_{DateTime.Now:yyyyMMdd}.xlsx";
            return File(bytes, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", fileName);
        }

        /// <summary>
        /// 导入BOM（优先 XLSX，兼容 CSV）。仅导入主信息。
        /// </summary>
        [HttpPost("import")]
        [RequestSizeLimit(50_000_000)]
        public async Task<ActionResult<ApiResult<object>>> Import([FromForm] IFormFile file)
        {
            if (file == null || file.Length == 0)
            {
                return new ApiResult<object> { Code = BaseEnums.BadRequest, Msg = "未选择文件或内容为空", Data = null! };
            }

            int ok = 0, fail = 0; var errors = new List<string>();

            async Task ParseRow(string[] cells, int displayRow)
            {
                // 期望列数 >= 11
                if (cells.Length < 11)
                {
                    fail++; errors.Add($"第{displayRow}行列数不足（{cells.Length}）");
                    return;
                }

                try
                {
                    string? bomCode = cells[0]?.Trim();
                    string? systemCode = cells[1]?.Trim();
                    string? productType = cells[2]?.Trim();
                    string? productCode = cells[3]?.Trim();
                    string? productName = cells[4]?.Trim();
                    string? specModel = cells[5]?.Trim();
                    string? unit = cells[6]?.Trim();
                    string? versionTag = cells[7]?.Trim();
                    string? isDefaultStr = cells[8]?.Trim();
                    string? dailyStr = cells[9]?.Trim();
                    string? remark = cells[10]?.Trim();

                    bool? isDefault = null;
                    if (!string.IsNullOrEmpty(isDefaultStr))
                    {
                        var s = isDefaultStr.ToLowerInvariant();
                        isDefault = s == "1" || s == "y" || s == "yes" || s == "true" || s == "是";
                    }
                    decimal? daily = null;
                    if (!string.IsNullOrEmpty(dailyStr) && decimal.TryParse(dailyStr, out var d)) daily = d;

                    var entity = new Bom
                    {
                        Id = Guid.NewGuid(),
                        Code = bomCode,
                        BomCode = bomCode,
                        SystemCode = string.IsNullOrWhiteSpace(systemCode) ? null : systemCode,
                        ProductType = string.IsNullOrWhiteSpace(productType) ? null : productType,
                        ProductCode = string.IsNullOrWhiteSpace(productCode) ? null : productCode,
                        ProductName = string.IsNullOrWhiteSpace(productName) ? null : productName,
                        Name = productName,
                        SpecModel = string.IsNullOrWhiteSpace(specModel) ? null : specModel,
                        Unit = string.IsNullOrWhiteSpace(unit) ? null : unit,
                        VersionTag = string.IsNullOrWhiteSpace(versionTag) ? null : versionTag,
                        IsDefault = isDefault,
                        DailyOutput = daily,
                        Remark = string.IsNullOrWhiteSpace(remark) ? null : remark,
                        CreatedAt = DateTime.Now,
                        CreatedBy = "import"
                    };

                    await _db.Boms!.AddAsync(entity);
                    ok++;
                }
                catch (Exception exRow)
                {
                    fail++; errors.Add($"第{displayRow}行：{exRow.GetBaseException().Message}");
                }
            }

            await using var tran = await _db.Database.BeginTransactionAsync();
            try
            {
                var ext = Path.GetExtension(file.FileName).ToLowerInvariant();
                if (ext == ".xlsx" || file.ContentType.Contains("spreadsheet"))
                {
                    using var ms = new MemoryStream();
                    await file.CopyToAsync(ms);
                    ms.Position = 0;
                    using var wb = new XLWorkbook(ms);
                    var ws = wb.Worksheets.FirstOrDefault();
                    if (ws == null) return new ApiResult<object> { Code = BaseEnums.BadRequest, Msg = "未找到工作表", Data = null! };
                    var firstRow = 2; // 跳过表头
                    var lastRow = ws.LastRowUsed()?.RowNumber() ?? 1;
                    for (int r = firstRow; r <= lastRow; r++)
                    {
                        var cells = Enumerable.Range(1, 11).Select(c => ws.Cell(r, c).GetString()).ToArray();
                        // displayRow = 实际行号
                        await ParseRow(cells, r);
                    }
                }
                else
                {
                    using var stream = file.OpenReadStream();
                    using var reader = new StreamReader(stream, Encoding.UTF8, true);
                    // 读表头
                    var header = await reader.ReadLineAsync();
                    if (header == null) return new ApiResult<object> { Code = BaseEnums.BadRequest, Msg = "文件内容为空", Data = null! };
                    string? line; int row = 1;
                    while ((line = await reader.ReadLineAsync()) != null)
                    {
                        row++;
                        if (string.IsNullOrWhiteSpace(line)) continue;
                        var cells = line.Split(',');
                        await ParseRow(cells, row);
                    }
                }

                await _db.SaveChangesAsync();
                await tran.CommitAsync();
                return new ApiResult<object>
                {
                    Code = BaseEnums.Success,
                    Msg = $"导入完成：成功 {ok} 条，失败 {fail} 条",
                    Data = new { success = ok, failed = fail, errors }
                };
            }
            catch (Exception ex)
            {
                await tran.RollbackAsync();
                return new ApiResult<object>
                {
                    Code = BaseEnums.InternalServerError,
                    Msg = ex.GetBaseException().Message,
                    Data = new { success = ok, failed = fail, errors }
                };
            }
        }
    }
}
